package nc.bs.gl.util;

import nc.bs.common.util.ArrayUtil;
import nc.bs.common.util.ObjectUtils;
import nc.bs.common.util.SQLUtil;
import nc.bs.common.util.pojo.TempAccountDetail;

/**
 * @author cloudwxjj
 * @date 2024/4/19-上午10:43
 */
public class GlDetailQueryUtil {


    private static final String ORG_BOOK_HOLDER = "${pk_accountingbook}";
    private static final String PERIOD_HOLDER="${pk_yearmonth}";
    public static final String VIEW_TABLE="v_temp_gl_detail";


    private static final String INITIAL_DATA_SQL="CASE WHEN borrowing='借方' " +
            " THEN " +
            " SUM(CASE WHEN pk_yearmonth=CONCAT(SUBSTR('${pk_yearmonth}',0,4),'-00') THEN localdebitamount ELSE 0 END)+" +
            " SUM(CASE WHEN pk_yearmonth<=to_char(add_months(to_date('${pk_yearmonth}','yyyy-mm'),-1),'yyyy-mm') AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localdebitamount ELSE 0 END)-" +
            "SUM(CASE WHEN pk_yearmonth<=to_char(add_months(to_date('${pk_yearmonth}','yyyy-mm'),-1),'yyyy-mm') AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localcreditamount ELSE 0 END)" +
            " WHEN borrowing='贷方' " +
            " THEN " +
            "  SUM(CASE WHEN pk_yearmonth=CONCAT(SUBSTR('${pk_yearmonth}',0,4),'-00') THEN localcreditamount ELSE 0 END)+" +
            "SUM(CASE WHEN pk_yearmonth<=to_char(add_months(to_date('${pk_yearmonth}','yyyy-mm'),-1),'yyyy-mm') AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localcreditamount ELSE 0 END)-" +
            "SUM(CASE WHEN pk_yearmonth<=to_char(add_months(to_date('${pk_yearmonth}','yyyy-mm'),-1),'yyyy-mm') AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localdebitamount ELSE 0 END)" +
            " END initial_data";
    private static final String LOCAL_DEBIT_AMOUNT="SUM(CASE WHEN pk_yearmonth='${pk_yearmonth}' THEN localdebitamount ELSE 0 END) localdebitamount";
    private static final String LOCAL_CREDIT_AMOUNT="SUM(CASE WHEN pk_yearmonth='${pk_yearmonth}' THEN localcreditamount ELSE 0 END) localcreditamount";
    private static final String LOCAL_DEBIT_AMOUNT_SUM="SUM(CASE WHEN pk_yearmonth<='${pk_yearmonth}' AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localdebitamount ELSE 0 END) localdebitamountsum";
    private static final String LOCAL_CREDIT_AMOUNT_SUM ="SUM(CASE WHEN pk_yearmonth<='${pk_yearmonth}' AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localcreditamount ELSE 0 END) localcreditamountsum";
    private static final String FINAL_DATA_SQL="CASE WHEN borrowing='借方' " +
            "   THEN " +
            "       -(SUM(CASE WHEN pk_yearmonth=CONCAT(SUBSTR('${pk_yearmonth}',0,4),'-00') THEN localdebitamount ELSE 0 END)+" +
            " SUM(CASE WHEN pk_yearmonth<='${pk_yearmonth}' AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localdebitamount ELSE 0 END)-" +
            " SUM(CASE WHEN pk_yearmonth<='${pk_yearmonth}' AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localcreditamount ELSE 0 END))" +
            "  WHEN borrowing='贷方' " +
            "  THEN " +
            "   SUM(CASE WHEN pk_yearmonth=CONCAT(SUBSTR('${pk_yearmonth}',0,4),'-00') THEN localcreditamount ELSE 0 END)+" +
            " SUM(CASE WHEN pk_yearmonth<='${pk_yearmonth}' AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localcreditamount ELSE 0 END)-" +
            " SUM(CASE WHEN pk_yearmonth<='${pk_yearmonth}' AND yearv=SUBSTR('${pk_yearmonth}',0,4) AND monthv<>'00' THEN localdebitamount ELSE 0 END)" +
            " END final_data ";


    public static String    buildQuerySQL(GlDetailQueryCondition condition){
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT ");
        if(ObjectUtils.isEmpty(condition.getSelectColumn())){
            throw new RuntimeException("select column is null");
        }
        String select = SQLUtil.selectToString(condition.getSelectColumn());
        builder.append(select);
        if(condition.isInitData()){
            builder.append(",").append(INITIAL_DATA_SQL.replace(PERIOD_HOLDER,condition.getYearMonth()));
        }
        if(condition.isDebitData()){
            builder.append(",").append(LOCAL_DEBIT_AMOUNT.replace(PERIOD_HOLDER,condition.getYearMonth()));
        }
        if(condition.isCreditData()){
            builder.append(",").append(LOCAL_CREDIT_AMOUNT.replace(PERIOD_HOLDER,condition.getYearMonth()));
        }
        if(condition.isDebitSumData()){
            builder.append(",").append(LOCAL_DEBIT_AMOUNT_SUM.replace(PERIOD_HOLDER,condition.getYearMonth()));
        }
        if(condition.isCreditSumData()){
            builder.append(",").append(LOCAL_CREDIT_AMOUNT_SUM.replace(PERIOD_HOLDER,condition.getYearMonth()));
        }
        if(condition.isFinalData()){
            builder.append(",").append(FINAL_DATA_SQL.replace(PERIOD_HOLDER,condition.getYearMonth()));
        }
        builder.append(" from ").append(VIEW_TABLE);
        if(!ObjectUtils.isEmpty(condition.getLeftJoin())){
            condition.getLeftJoin().forEach(a-> builder.append(" left join ").append(a));
        }
        builder.append(" where ");
        builder.append("v_temp_gl_detail."+TempAccountDetail.cpk_accountingbook).append(" = '").append(condition.getAccountIngBook()).append("'");
        if(condition.isNeedInitNov()){
//            builder.append(" and v_temp_gl_detail.pk_yearmonth IN (CONCAT(SUBSTR('")
//                    .append(condition.getYearMonth()).append("', 0, 4), '-00'),'")
//                    .append(condition.getYearMonth()).append("') ");
            builder.append(" and v_temp_gl_detail.pk_yearmonth between CONCAT(SUBSTR('")
                    .append(condition.getYearMonth()).append("', 0, 4), '-00') and '")
                    .append(condition.getYearMonth()).append("' ");
        }else{
            builder.append(" and v_temp_gl_detail.pk_yearmonth = '").append(condition.getYearMonth()).append("'");
        }
        if(!ObjectUtils.isEmpty(condition.getWhereColumn())){
            builder.append(" and ").append(SQLUtil.whereToString(condition.getWhereColumn()));
        }
        if(!ObjectUtils.isEmpty(condition.getWhereNotColumn())){
            builder.append(" and ").append(SQLUtil.whereToString(condition.getWhereNotColumn()));
        }
        if(!ObjectUtils.isEmpty(condition.getExtraWhereSQL())){
            builder.append(" and ").append(condition.getExtraWhereSQL());
        }
        if(!ObjectUtils.isEmpty(condition.isDelSy())){
            if(condition.isDelSy()){
                builder.append(" and voucherkindv <> '4' ");
            }
        }
        if(!ObjectUtils.isEmpty(condition.getGroupColumn())){
            builder.append(" group by ").append(SQLUtil.selectToString(condition.getGroupColumn()));
        }
        return builder.toString();
    }



}
